import datetime
import os
import json
import pymysql
from flask import (
    Blueprint,
    jsonify,
    request,
    current_app,
    send_file,
    send_from_directory,
)
import utilsapp.excel_util
from utilsapp import utils
from utilsapp.mysql_con import (
    db_pagelist,
    db_connect,
    db_operation,
    db_insert,
    db_delete,
    db_update,
    db_list,
    db_fetchone,
)
from utilsapp.common import assign_value, parsing_data_id, find_str

device = Blueprint("device", __name__)
staic_url = "/static"


@device.route("/list", methods=["post"])
def aa():
    a = request.json
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    device_name = assign_value(a, "device_name")
    type_id = assign_value(a, "type_id")
    status = assign_value(a, "status")
    size = assign_value(a, "size", 9)
    sql = f""" SELECT d.id as device_id,d.status,d.device_name,d.device_code,dt.name as type_name,r1.name as zone_name,r2.name as factory_name,dt.icon,
    w.warn_count, s.sensor_count,concat('{staic_url}',d.device_img) as device_img,w.max_warn_level as warn_level,d.type_id,d.zone_id,d.factory_id,
    d.company_id,d.group_id
    from device d
    LEFT JOIN regionlevel r1 on d.zone_id = r1.id
    LEFT JOIN regionlevel r2 on d.factory_id = r2.id
    LEFT JOIN (SELECT device_id,count(id) as warn_count,MAX(warn_level) as max_warn_level from warn where is_show=1 and notice in (0,1) GROUP BY device_id) as w on w.device_id = d.id
    LEFT JOIN device_type dt on dt.id =d.type_id
    LEFT JOIN (SELECT device_id,count(id) as sensor_count from sensor GROUP BY device_id ) as s on s.device_id = d.id
	"""
    query = " where 1=1 "
    if scoped != "":
        query += f" and d.{scoped} "
    if device_name != "":
        query += f" and device_name like '%{device_name}%' "
    if type_id != "":
        query += f" and type_id = {type_id} "
    if status != "":
        query += f" and status = {status}"
    sql_1 = f"select * from ({sql}) as d {query} order by CASE WHEN status = 1 THEN 0 ELSE 1 END, zone_id,device_name,device_id asc"

    res = db_pagelist(sql_1, a["page"], size)
    sql_1 = f"select count(device_id) from ({sql}) as d {query} and status = 0 "
    sql_2 = f"select count(device_id) from ({sql}) as d {query} and status = 1 "
    sql_3 = f"select count(device_id) from ({sql}) as d {query} and status = 2 "
    sql_4 = f"select count(device_id) from ({sql}) as d {query} and status = 3 "
    num1 = db_fetchone(sql_1)
    num2 = db_fetchone(sql_2)
    num3 = db_fetchone(sql_3)
    num4 = db_fetchone(sql_4)
    res["stat"] = {
        "stoppage": num1["data"],  # 停机
        "normal": num2["data"],  # 正常
        "warn": num3["data"],  # 告警
        "offline": num4["data"],  # 离线
    }
    return res



# 根据测点 id 保存 测点 位置以及描述 位置
@device.route("/save/graph", methods=["post"])
def save_graph():
    sensor = request.json["sensor"]
    for item in sensor:
        obj = {"id": item["id"], "graph": assign_value(item, "graph", "[]")}
        res = db_update("sensor", obj, f"where id =%s", {"mid": obj["id"]})
        if res["code"] != 0:
            return {"code": -1, "msg": "修改失败"}
    return {"code": 0, "msg": "修改成功"}


# 查询设备详情 pc
@device.route("/details")
def get_device_detail():
    device_id = int(request.args.get("id"))
    sql = f"""SELECT d.id, d.device_name, t.name AS device_type, concat("/static",d.device_img)  AS imgurl, d.`status`, f.name AS factory_name, z.name AS zone_name, d.nameplate,
    IFNULL(w.warn_num, 0) AS warn_num, IFNULL(w1.warn_num, 0) AS level_1, IFNULL(w2.warn_num, 0) AS level_2,IFNULL(w3.warn_num, 0) AS level_3,
		IFNULL(n.notice_num,0) as notice_num,	IFNULL(n1.notice_num,0) as handled,IFNULL(n2.notice_num,0) as unHandled
FROM device d
LEFT JOIN regionlevel z ON d.zone_id = z.id
LEFT JOIN regionlevel f ON d.factory_id = f.id
LEFT JOIN device_type t ON t.id = d.type_id
LEFT JOIN (
    SELECT COUNT(id) AS warn_num, device_id 
    FROM warn 
    WHERE is_show = 1 
    GROUP BY device_id
) w ON w.device_id = d.id
LEFT JOIN (
    SELECT COUNT(id) AS warn_num, device_id 
    FROM warn 
    WHERE is_show = 1 AND warn_level = 1 
    GROUP BY device_id
) w1 ON w1.device_id = d.id
LEFT JOIN (
    SELECT COUNT(id) AS warn_num, device_id 
    FROM warn 
    WHERE is_show = 1 AND warn_level = 2
    GROUP BY device_id
) w2 ON w2.device_id = d.id
LEFT JOIN (
    SELECT COUNT(id) AS warn_num, device_id 
    FROM warn 
    WHERE is_show = 1 AND warn_level = 3
    GROUP BY device_id
) w3 ON w3.device_id = d.id
LEFT JOIN (
		SELECT count(id) as notice_num,device_id
		from notice
		GROUP BY device_id
) n on n.device_id = d.id
LEFT JOIN (
		SELECT count(id) as notice_num,device_id
		from notice
		where status = 1
		GROUP BY device_id
) n1 on n1.device_id = d.id
LEFT JOIN (
		SELECT count(id) as notice_num,device_id
		from notice
		where status = 2
		GROUP BY device_id
) n2 on n2.device_id = d.id
WHERE d.id = {device_id}
"""
    res = db_connect(sql)
    sensor = db_connect(
        f"select s.id,s.sensor_name,s.direction from sensor s where s.device_id={device_id}"
    )["data"]
    res["data"][0]["sensor"] = sensor
    return res


# 查询device列表
@device.route("/getlist", methods=["POST"])
def get_device_list():
    a = request.json
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    device_name = assign_value(a, "device_name")
    sql = f"""select d.id,d.device_name,d.device_code,d.config,t.name as type_name,d.type_id,d.device_img as imgurl,d.`status`,g.name as group_name ,c.name as 
                company_name,f.name as factory_name,z.name as zone_name,tb.c sensor_count
                from device d
                LEFT JOIN regionlevel z on z.id=d.zone_id
                LEFT JOIN regionlevel f on f.id=d.factory_id
                LEFT JOIN regionlevel c on c.id=d.company_id
                LEFT JOIN regionlevel g on g.id=d.group_id
                LEFT JOIN device_type t on t.id=d.type_id
                LEFT JOIN (SELECT COUNT(id) c ,device_id FROM sensor GROUP BY device_id ) tb on tb.device_id=d.id"""
    query = " where 1=1 "
    if scoped != "":
        query += f" and d.{scoped}"
    if device_name != "":
        query += f" and d.device_name like '%{device_name}%'"
    sql = sql + query + " " + " ORDER BY d.device_name"

    res = db_pagelist(sql, request.json["page"], request.json["size"])
    return res


# 添加设备
@device.route("/add", methods=["post"])
def device_add():
    keys_not_add = ["id", "create_time", "sensors", "areaids"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    addModel["status"] = 3
    res = db_insert("device", addModel)
    if res["code"] < 0:
        return {"code": -1, "msg": "添加失败", "data": res}
    sensors = request.json["sensors"]
    keys_not_add = ["id", "eigenvalue", "create_time"]
    addSensors = []
    for item in sensors:
        if len(item["sensor_name"]) <= 0:
            continue
        m = {key: item[key] for key in item if key not in keys_not_add}
        m["device_id"] = res["lastrowid"]
        m["group_id"] = addModel["group_id"]
        m["company_id"] = addModel["company_id"]
        m["factory_id"] = addModel["factory_id"]
        m["zone_id"] = addModel["zone_id"]
        m["eigenvalue"] = ",".join(item["eigenvalue"])
        addSensors.append(m)

    res = db_insert("sensor", addSensors)
    utilsapp.excel_util.create_table(
        request.json["sensors"], current_app.config["settings"]["mysql"]
    )
    if res["Affected_rows"] <= 0:
        return {"code": -1, "msg": "测点添加失败", "data": res}

    return {"code": 0, "msg": "添加成功", "data": res}


# 删除设备
@device.route("/delete", methods=["post"])
def sensor_delete():
    id_ = request.json.get(
        "id"
    )  # 使用.get()方法以安全方式获取'id'字段的值，避免出现KeyError异常
    # res = db_connect(
    #     f"""SELECT * FROM sensor
    #     where device_id  = {id_}"""
    # )
    # if res["code"] != -1:
    #     sensors = res["data"]
    #     utilsapp.excel_util.drop_table(sensors, current_app.config["settings"]["mysql"])
    merged_dict = {}
    res1 = db_delete("sensor", "where device_id = %s", {"mid": id_})
    merged_dict.update(res1)
    res2 = db_delete("device", "where id = %s", {"mid": id_})
    merged_dict.update(res2)
    res3 = db_delete("warn", f"WHERE device_id=%s", {"device_id": id_})
    merged_dict.update(res3)
    return merged_dict


# 修改设备
@device.route("/edit", methods=["post"])
def device_change():
    try:
        mid = request.json["id"]
        keys_not_add = ["id", "create_time", "sensors", "areaids", "nameplate","company_name","factory_name","group_name","zone_name","sensor_count","imgurl","type_name"]
        addModel = {
            key: request.json[key] for key in request.json if key not in keys_not_add
        }
        return db_update("device", addModel, f"WHERE id = %s", {"mid": mid})
    except Exception as e:
        return utils.fail("修改失败", err=e)



@device.route("/download", methods=["POST", "GET"])
def download_file():
    try:
        filename = "测点导入模板.xlsx"
        script_dir = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
        filePath = script_dir + "/utilsapp/" + filename
        return send_file(filePath, as_attachment=True, attachment_filename=filename)
    except Exception as e:
        return jsonify({"code": -1, "msg": "下载异常"}), 200


@device.route("/getDevice25d")
def getDevice25dpc():
    device_id = int(request.args.get("id"))
    sql1 = f"""select device_name,'' as imgurl from device where id={device_id} """
    res1 = db_connect(sql1)
    sql2 = f"""select s.id,s.sensor_name,d2.device_img as device_img,d2.tags,d2.des,d2.x_per,d2.y_per,w.warn_level,w.id as warn_id,
    w.title as warn_title,d2.description as description,DATE_FORMAT(w.create_time,'%Y-%m-%d %H:%i:%s') as create_time  from 
    sensor s inner join device_25d  d2  on  s.id = d2.cedianId  left join warn w on d2.alarm_id = w.id  WHERE s.device_id 
    = {device_id}"""
    res2 = db_connect(sql2)
    for item in res2["data"]:
        if item["device_img"]:
            res1["data"][0]["imgurl"] = staic_url + item["device_img"]
    res1["data"][0]["sensor"] = res2["data"]
    return res1


# 获取设备2.5D
@device.route("/getDevice25d/pc")
def device25d_get():
    device_id = int(request.args.get("id"))
    sql1 = f"""SELECT d.id as device_id,d.device_name,d.device_25D_img as imgurl from device d where id = {device_id}"""
    res1 = db_connect(sql1)
    sql2 = f"""	SELECT s.id as sensor_id,s.group_id,s.direction,st.single,s.address,s.port,s.status,st.topic,st.sys_eig,st.characters,s.graph,s.sensor_name, IFNULL(w.warn_level,0) as warn_level,w.pred_time
		from sensor s
		LEFT JOIN  (
		SELECT sensor_id,max(pred_time) as pred_time, max(warn_level) as warn_level from warn  where
		 is_show = 1 and notice in (0,1) GROUP BY sensor_id
		) w on w.sensor_id = s.id 
		LEFT JOIN sensor_type st on st.id = s.sensor_type
		where s.device_id ={device_id}"""
    res2 = db_connect(sql2)
    res1["data"][0]["sensor"] = res2["data"]
    if res1["data"][0]["imgurl"]:
        res1["data"][0]["imgurl"] = staic_url + res1["data"][0]["imgurl"]
    return res1


# 上传设备2.5D图
@device.route("/upload/25D", methods=["post"])
def upload_25D():
    mid = request.json["id"]
    return db_update("device", request.json, f"WHERE id = %s", {"mid": mid})


def get_filter(reg):
    if reg.get("type") == "group":
        return f" group_id={reg.get('id')}"
    if reg.get("type") == "company":
        return f" company_id={reg.get('id')}"
    if reg.get("type") == "factory":
        return f" factory_id={reg.get('id')}"
    if reg.get("type") == "zone":
        return f" zone_id={reg.get('id')}"
    return "1=1"


# 根据区域 查询设备列表 name + id
@device.route("/zone/getlist", methods=["get"])
def device_zone_getlist():
    zone_id = request.args.get("zone_id")
    sql = f"""select id as device_id,device_name,zone_id,factory_id,company_id,group_id from device where zone_id={zone_id}"""
    res = db_connect(sql)

    return {"data": res["data"], "code": 0}


@device.route("/getdetails")
def get_device_page_detail():
    device_id = int(request.args.get("id"))
    res = db_connect(f"""select * from device d where d.id={device_id}""")
    if res["code"] == -1 or len(res["data"]) <= 0:
        return res
    device = res["data"][0]

    res = db_connect(
        f"""SELECT a.address,a.company_id,a.device_id,a.direction,a.eigenvalue,
        a.factory_id,a.fs,a.group_id,a.port,a.sensor_name,a.sensor_type,a.zone_id FROM sensor a
        where device_id  = {device_id}"""
    )
    if res["code"] != -1:
        device["sensors"] = res["data"]

    result = {"msg": "查询成功", "code": 0, "data": device}
    return result


@device.route("/nameplate/edit", methods=["post"])
def device_nameplate_edit():
    mid = request.json["id"]
    return db_update("device", request.json, f"WHERE id = %s", {"mid": mid})


@device.route("/getDevice", methods=["POST"])
def device_device():
    id = request.json.get("id")
    scoped = parsing_data_id(id, request)

    sql = f"""
        SELECT 
    a.id, 
    a.device_name, 
    a.days, 
    MAX(a.warn_level) AS warn_level, 
    a.device_img, 
    a.company_name 
FROM 
    (
        SELECT 
            d.id, 
            d.device_name, 
            TIMESTAMPDIFF(DAY, d.create_time, NOW()) AS days, 
            w.warn_level, 
            d.device_img, 
            r.name AS company_name
        FROM 
            device d
            LEFT JOIN warn w ON w.device_id = d.id
            LEFT JOIN device_type dt ON d.type_id = dt.id
            LEFT JOIN regionlevel r ON d.company_id = r.id
        WHERE 
            d.{scoped}
    ) a 
GROUP BY 
    a.id, 
    a.device_name, 
    a.days, 
    a.device_img, 
    a.company_name"""
    res = db_connect(sql)

    for item in res["data"]:
        id_ = item["id"]
        warn = db_connect(
            f"select * from warn w where w.device_id={id_} and w.notice=0 and w.is_show =1"
        )["data"]
        item["warn"] = warn
        if len(warn) == 0:
            item["warn_level"] = None
        else:
            max = 0
            for i in warn:
                max = max > i["warn_level"] and max or i["warn_level"]
            item["warn_level"] = max
    return res


@device.route("/getDeviceInfo", methods=["POST"])
def device_device_info():
    id = request.json.get("id")
    sql = f"""SELECT 
    device.id,device.device_name,device_type.name as device_type,device.device_img,
    datediff(now(),device.create_time) as days,f.name as factory_name,z.name as zone_name ,
    count(sensor.sensor_name) as sensor_number,max(warn.warn_level) as warn_level
    FROM device 
    left join regionlevel f on device.factory_id = f.id
    left join regionlevel z on device.zone_id = z.id
    left join device_type on device.type_id = device_type.id
    left join sensor on device.id = sensor.device_id
    left join warn on device.id = warn.device_id  and warn.is_handled=0
    where device.id = {id}"""
    res = db_connect(sql)

    if res["data"] is not None:
        if res["data"][0]["warn_level"] is None:
            res["data"][0]["warn_level"] = "正常"
        else:
            pass
        result = {"msg": "查询成功！", "status": 200, "data": res["data"][0]}
    else:
        result = {"msg": "查询失败！", "status": 202}
    return result


@device.route("/getSensor", methods=["GET"])
def get_sensor():
    device_id = int(request.args.get("id"))
    res = db_connect(
        f"""SELECT a.address,a.company_id,a.device_id,a.direction,a.eigenvalue,
        a.factory_id,a.fs,a.group_id,a.port,a.sensor_name,a.sensor_type,a.zone_id,a.id as id FROM sensor a
        where device_id  = {device_id}"""
    )
    return res

@device.route("/getSensor", methods=["POST"])
def device_sensor():
    id = request.json.get("id")
    sql = """
        SELECT sensor.id,sensor.sensor_name,sensor.direction,sensor.address,sensor.port ,sensor.status,sensor_type.sys_eig,sensor_type.characters, sensor_type.topic, sensor_type.single,regionlevel.proj_no  FROM 
        sensor,sensor_type,regionlevel where sensor.sensor_type = sensor_type.id and sensor.group_id = regionlevel.id and sensor.device_id = {}""".format(
        id
    )
    if db_connect(sql)["data"]:
        result = {"msg": "查询成功！", "status": 200, "data": db_connect(sql)["data"]}
    else:
        result = {"msg": "查询失败！", "status": 202}
    return result


def get_conn(db_name=None, dbconfig=None):
    d = current_app.config["settings"]["mysql"]
    db_name = d["dbname"] if db_name is None else db_name
    dbconfig = d if dbconfig is None else dbconfig

    return pymysql.connect(
        host=dbconfig["host"],
        user=dbconfig["user"],
        port=dbconfig["port"],
        password=dbconfig["password"],
        db=db_name,
        charset="utf8",
        connect_timeout=10,
        read_timeout=10,
        write_timeout=10,
    )


@device.route("/getSensors")
def get_sensors():
    device_id = int(request.args.get("id"))
    sensors = []
    res = db_connect(
        f"""SELECT a.address,a.company_id,a.device_id,a.direction,a.eigenvalue,
        a.factory_id,a.fs,a.group_id,a.port,a.sensor_name,a.sensor_type,a.zone_id,a.id as id FROM sensor a
        where device_id  = {device_id}"""
    )
    if res["code"] != -1:
        sensors = res["data"]
    if res["code"] == -1 | len(sensors) == 0:
        result = {"msg": "查询成功", "code": 0, "data": sensors}
    conn = get_conn("ieplat")
    cur = conn.cursor()
    cur.execute("SHOW TABLES;")
    datas = cur.fetchall()
    tbs = [t[0] for t in datas]
    for item in sensors:
        try:
            name = f'd_{item["port"]}_{item["address"]}'
            item["dbs"] = []
            for t in tbs:
                if name in t:
                    q = f"""SELECT COUNT(*) c,max(time) t FROM {t}"""
                    cur.execute(q)
                    datas = cur.fetchall()
                    fields = [field[0] for field in cur.description]
                    res = [dict(zip(fields, result)) for result in datas]
                    item["dbs"].append(
                        {"name": t, "count": res[0]["c"], "time": res[0]["t"]}
                    )
        except Exception as e:
            cur.close()
            return utils.fail("网络异常，稍后重试", "", e)
    cur.close()
    result = {"msg": "查询成功", "code": 0, "data": sensors}
    return result


@device.route("/getssr", methods=["post"])
def get_ssr():
    a = request.json
    device_id = assign_value(a, "device_id", 0)
    sql = f"""SELECT ssr.*,d.device_name
from ssr 
LEFT JOIN device d on d.id =ssr.device_id where d.id = {device_id}"""
    return db_pagelist(sql, a["page"])


# 油液相关接口
@device.route("/getoil/device", methods=["post"])
def aasdes291():
    page = assign_value(request.json, "page", 1)
    size = assign_value(request.json, "size", 20)
    sql = f"""
    SELECT DISTINCT  addr  FROM youye
    """
    return db_pagelist(sql, page, size)


@device.route("/getoil", methods=["post"])
def aasdes21():
    try:
        a = request.json
        key = assign_value(a, "key")
        blockage_point = []
        cycle_time = []
        statistical_data = {}
        # 遍历字典中的每个项目
        for key1, value in current_app.redis.hgetall(f"{key}#blockage_point").items():
            parsed_data = json.loads(value)
            max_value = parsed_data.pop("max")
            item_data = {"name": key1, "maxp": max_value, "monp": parsed_data}
            # 添加到结果列表中
            blockage_point.append(item_data)
        for key2, value in current_app.redis.hgetall(f"{key}#cycle_time").items():
            if not value:
                continue
            parsed_data = json.loads(value)
            # 添加项目名称到数据中
            parsed_data["name"] = int(key2)
            cycle_time.append(parsed_data)
        for key3, value in current_app.redis.hgetall(f"{key}#statistical_data").items():

            statistical_data[key3] = json.loads(value)
        data = {
            "blockage_point": blockage_point,
            "cycle_time": cycle_time,
            "statistical_data": statistical_data,
        }

        return utils.ok(data)
    except Exception as e:
        return {"code": -1, "data": None, "msg": str(e)}


@device.route("/oil/mysql", methods=["post"])
def sdda():
    a = request.json
    page = assign_value(request.json, "page", 1)
    size = assign_value(request.json, "size", 20)
    start_time = assign_value(request.json, "start_time")
    end_time = assign_value(request.json, "end_time")
    addr = assign_value(a, "addr")
    sql = f"SELECT * from youye where addr = '{addr}'  "
    query = ""
    if start_time != "":
        query += f" and time >= '{start_time}'"
    if end_time != "":
        query += f" and time <= '{end_time}'"
    sql = sql + query + " order by time desc "

    res = db_pagelist(sql, page, size)
    if "data" not in res:
        res["data"] = []
    modified_data = []
    for item in res["data"]:
        if isinstance(item, dict):
            value1 = json.loads(item["value1"])
            value2 = json.loads(item["value2"])
            modified_item = {**item, **value1, **value2}
            modified_data.append(modified_item)
    res["data"] = modified_data
    res["sql"] = sql
    return res


@device.route("/set/manpar", methods=["post"])
def as222():
    try:
        key = assign_value(request.json, "key")
        value = assign_value(request.json, "value")

        current_app.redis.set(key, value)
        d = current_app.redis.get(key)
        return utils.ok(d)
    except Exception as e:
        return {"code": -1, "data": None, "msg": str(e)}


@device.route("/img25/list", methods=["post"])
def s551():
    sql = "select * from sys_dimg where 1 = 1 "
    a = request.json
    name = assign_value(a, "name")
    type_ = assign_value(a, "type")
    page = assign_value(a, "page", 1)
    size = assign_value(a, "size", 20)
    if name != "":
        sql += f"and name like '%{name}%'"
    if type_ != "":
        sql += f" and type = {type_}"
    return db_pagelist(sql, page, size)


@device.route("/img25/add", methods=["post"])
def role_a22dd():
    return db_insert("sys_dimg", request.json)


@device.route("/img25/edit", methods=["post"])
def role_a1111122dd():
    # return db_insert("sys_dimg", request.json)
    keys_not_add = ["id"]
    id = request.json["id"]
    addModel = {
        key: request.json[key] for key in request.json if key not in keys_not_add
    }
    return db_update("sys_dimg", addModel, f"WHERE id=%s", {"id": id})


@device.route("/img25/delete", methods=["post"])
def report_ssdelete():
    id_ = request.json['id']
    return db_delete("sys_dimg", f"where id = %s", {'mid': id_})